﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using GU.WebERP.Model;
using GU.WebERP.Dict;
using System.Data;
using System.Collections;
using DALBase;
using YGOP.ESB.Log;
using System.Configuration;

namespace GU.WebERP.DAL
{
    public class DAL_Fct_OfficeBuildings : JnsFramework.Core.TableOperating
    {
        readonly JnsFramework.Core.ExecCustomizeSQL _exec = new JnsFramework.Core.ExecCustomizeSQL();
        readonly Model_Fct_OfficeBuildings _modelFct_OfficeBuildings = new Model_Fct_OfficeBuildings();

        public DAL_Fct_OfficeBuildings()
        {
            base.DBKey = "GU.ERPDb";
        }

        protected override JnsFramework.Common.IModel GetTableModel()
        {
            return _modelFct_OfficeBuildings;
        }

        /// <summary>
        /// 获取办公楼城市列表
        /// </summary>
        /// <returns></returns>
        public DataTable GetCityList()
        {
            string sql = @"select BId,City from Fct_OfficeBuildings where disabled=0 and grade = 1";

            try
            {
                return _exec.Query(sql);
            }
            catch (Exception ex)
            {
                LogManager.WriteDbError("获取办公楼城市列表", sql, null, ex);
            }
            return null;
        }

        /// <summary>
        /// 获取办公楼城市区列表
        /// </summary>
        /// <returns></returns>
        public DataTable GetDistrictList(string cityName)
        {
            string sql = @"select BId,District from Fct_OfficeBuildings where disabled=0 and grade=2 and City =@City";

            try
            {
                Hashtable hs = new Hashtable();
                hs.Add("@City", cityName);

                return _exec.Query(sql, hs);
            }
            catch (Exception ex)
            {
                LogManager.WriteDbError("获取办公楼城市区列表", sql, cityName, ex);
            }
            return null;
        }

        /// <summary>
        /// 删除办公楼
        /// </summary>
        /// <returns></returns>
        public bool DeleteBuilding(string bid)
        {
            string sql = @"update Fct_OfficeBuildings set disabled=1 where bid =@bid";

            try
            {
                Hashtable hs = new Hashtable();
                hs.Add("@bid", bid);

                return _exec.ExecuteSQL(sql, hs) > 0;
            }
            catch (Exception ex)
            {
                LogManager.WriteDbError("删除办公楼", sql, bid, ex);
            }
            return false;
        }

        /// <summary>
        /// 根据BId获取办公楼对象
        /// </summary>
        /// <returns></returns>
        public DataTable GetBuildingModel(string bid)
        {
            string sql = @"select * from Fct_OfficeBuildings where disabled=0 and bid =@bid";

            try
            {
                Hashtable hs = new Hashtable();
                hs.Add("@bid", bid);

                return _exec.Query(sql, hs);
            }
            catch (Exception ex)
            {
                LogManager.WriteDbError("根据BId获取办公楼实体", sql, bid, ex);
            }
            return null;
        }

        /// <summary>
        /// 验证办公楼是否存在
        /// </summary>
        /// <returns></returns>
        public bool ExistsBuilding(string city, string district, string buildingName)
        {
            string sql = @"select 1 from Fct_OfficeBuildings where city=@city and district=@district and buildingName like @buildingName ";

            try
            {
                Hashtable hs = new Hashtable();
                hs.Add("@city", city);
                hs.Add("@district", district);
                hs.Add("@buildingName", "%" + buildingName + "%");

                return _exec.ExecuteScalar(sql, hs) != null;
            }
            catch (Exception ex)
            {
                LogManager.WriteDbError("验证办公楼是否存在", sql, null, ex);
            }
            return false;
        }

        /// <summary>
        /// 获取列表
        /// </summary>
        /// <param name="cityName">城市名称</param>
        /// <param name="districtID">区ID</param>
        /// <param name="buildingName">办公楼名称，like</param>
        /// <param name="pageSize"></param>
        /// <param name="pageIndex"></param>
        /// <param name="sort"></param>
        /// <returns></returns>
        public DataSet GetList(string cityName, string district, string buildingName, int pageSize, int pageIndex, string sort)
        {
            try
            {
                StringBuilder sbWhere = new StringBuilder();
                sbWhere.Append(" and disabled=0 ");
                sbWhere.Append(" and grade=3 ");
                if (!string.IsNullOrWhiteSpace(cityName))
                {
                    sbWhere.Append(" and City =@City");
                }

                if (!string.IsNullOrWhiteSpace(district))
                {
                    sbWhere.Append(" and District=@District");
                }

                if (!string.IsNullOrWhiteSpace(buildingName))
                {
                    sbWhere.Append(" and BuildingName like @BuildingName");
                }

                StringBuilder sb = new StringBuilder();
                sb.Append("select * from (");
                sb.Append("                 select ");
                sb.AppendFormat("                   ROW_NUMBER() over(order by @Sort) 'pagenum' ");
                sb.Append("                         ,* from Fct_OfficeBuildings where 1=1 ");
                sb.Append(sbWhere);
                sb.Append(") t");
                sb.Append(" where pagenum > " + (pageIndex) * pageSize + " and pagenum<=" + (pageIndex + 1) * pageSize + " ");

                StringBuilder sbCount = new StringBuilder();
                sbCount.Append("select count (1) from Fct_OfficeBuildings where 1=1 ");
                sbCount.Append(sbWhere);

                Hashtable ht = new Hashtable();
                ht.Add("@City", cityName);
                ht.Add("@District", district);
                ht.Add("@BuildingName", "%" + buildingName + "%");
                if (!string.IsNullOrWhiteSpace(sort))
                {
                    ht.Add("@Sort", sort);
                }
                else
                {
                    ht.Add("@Sort", "Fct_OfficeBuildings.Rec_CreateTime desc");
                }

                DataTable count = _exec.Query(sbCount.ToString(), ht);
                DataTable dt = _exec.Query(sb.ToString(), ht);

                count.TableName = "total";
                dt.TableName = "data";

                DataSet ds = new DataSet();
                ds.Tables.Add(count.Copy());
                ds.Tables.Add(dt.Copy());

                return ds;
            }
            catch (Exception ex)
            {
                LogManager.WriteDbError("获取办公楼列表", "", "", ex);
            }
            return null;
        }

        /// <summary>
        /// 添加办公楼
        /// </summary>
        /// <returns></returns>
        public bool AddBuilding(Hashtable hs)
        {
            string str = "(select max(Bid)+1 from Fct_OfficeBuildings where Grade=3 and city='" + hs["City"] + "')";

            string sql = @"  insert into Fct_OfficeBuildings 
                                        (
                                            BId,Grade,City,District,BuildingName,PinYin,Address,BusinessArea,ParentId,Rec_CreateBy,Disabled,Rec_CreateTime,
                                            Rec_ModifyTime,IsZxs,AreaId
                                        )
                               values
                                        (
                                            {0},3,@City,@District,@BuildingName,@PinYin,@Address,@BusinessArea,@ParentId,@Rec_CreateBy,0,getdate(),
                                            getdate(),@IsZxs,@AreaId
                                        )
                                  ";
            try
            {
                return _exec.ExecuteSQL(string.Format(sql, str), hs) > 0;
            }
            catch (Exception ex)
            {
                LogManager.WriteDbError("添加办公楼：", sql, null, ex);
            }
            return false;
        }

        /// <summary>
        /// 更新楼宇
        /// </summary>
        /// <param name="hs"></param>
        /// <returns></returns>
        public bool UpdateBuilding(Hashtable hs)
        {
            string sql = @"update Fct_OfficeBuildings set
                                District=@District,
                                BuildingName=@BuildingName,
                                PinYin=@PinYin,
                                Address=@Address,
                                City=@City,
                                ParentId=@ParentId,
                                BusinessArea=@BusinessArea,
                                Rec_ModifyTime=getdate(),
                                Rec_ModifyBy=@Rec_ModifyBy 
                                where BId=@BId
                           ";
            try
            {
                return _exec.ExecuteSQL(sql, hs) > 0;
            }
            catch (Exception ex)
            {
                LogManager.WriteDbError("更新楼宇 ", sql, null, ex);
            }
            return false;
        }
    }
}
